﻿//****************************************************************************//
//
// @File: Analyse.cs
// @Owner: lyudmilf
// @Test: 
//
// Purpose:
//
//      Methods to analyze each error sate
//
// Notes:
//      
//      
//
//****************************************************************************//

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml.XPath;
using SSAT.Forms;
using SSAT.Utils;

namespace SSAT.ATSDriver
{
    internal static class Analyse
    {
        private const string StatisticsSeparator = ":        ";
        private const string StatisticsValuesSeparator = ", ";

        public static void LoginFailedDefault(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            //Error: if we don't know what to do - just print out the error message
            WriteErrorAndTime(errorReader, atsReport);
        }

        private static void WriteErrorAndTime(DataRow errorReader, ReportDetails atsReport)
        {
            atsReport.Write(errorReader[Constants.AtsDriver.ErrorMessageColumn].ToString());
            atsReport.WriteFormat(ATSDriverResources.ErrorTime, errorReader[Constants.AtsDriver.ErrorTimeColumn].ToString());
        }

        /// <summary>
        /// Error: 18456, State: 1.
        /// </summary>
        public static void LoginFailedDefaultNoOutput(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            //We don't want to print out default message teturned to client - it is not informative
            return;
        }


        /// <summary>
        /// Error: 18456, State: 2
        /// </summary>
        public static void LoginFailedSecurityGetLogin1(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            //Call LoginFailedSecGetLogin2 for now
            LoginFailedSecurityGetLogin2(errorReader, serverConnection, atsReport);
        }

        /// <summary>
        /// Error: 18456, State: 3,4
        /// </summary>
        public static void LoginFailedSecurityUnprotectMem(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);

            //Let's look at the failed APIs
            AnalyseErrors.CheckApiFailures(errorReader, atsReport, true);
        }

        /// <summary>
        /// Error: 18456, State: 5
        /// </summary>
        public static void LoginFailedSecurityGetLogin2(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            SqlDataReader serverReader = null;
            try
            {
                SqlCommand serverCommand = new SqlCommand(Queries.SelectFromServerPrincipals, serverConnection);
                serverReader = serverCommand.ExecuteReader();

                atsReport.Write(ATSDriverResources.YouAreTryingToUseSQLServerAuthenitification);
                atsReport.Write(ATSDriverResources.VerifyLoginSpelling);
                while (serverReader.Read())
                {
                    atsReport.Write(serverReader.GetValue(0).ToString());
                }
                CloseReader(serverReader);

                string userNameProvided = GetUserName(errorReader);
                serverCommand.CommandText = string.Format(Queries.SelectSOUNDEXofPrincipals, userNameProvided);
                serverReader = serverCommand.ExecuteReader();
                while (serverReader.Read())
                {
                    atsReport.Write(ATSDriverResources.DidYouMeanThisLogin);
                    atsReport.Write(serverReader.GetValue(0).ToString());
                }
                CloseReader(serverReader);

                if (userNameProvided.Contains(Constants.System.DomainSeparator))
                {
                    atsReport.Write(ATSDriverResources.IfYouAreUsingWindowsLoginCheckTrustedConnection);
                }
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }


        /// <summary>
        /// Error: 18456, State: 6
        /// </summary>
        public static void LoginFailedSecurityLoginType(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            atsReport.Write(ATSDriverResources.YouAreTryingToUseSQLAuthWhileUsingWindowsAccount);

            string userNameProvided = GetUserName(errorReader);
            atsReport.WriteFormat(ATSDriverResources.WindowsAccountDoesntExistOnThisInstance, userNameProvided);
            atsReport.WriteFormat(ATSDriverResources.YouMustUseTrustedConnectionToLoginAs, userNameProvided);
        }

        private static string GetUserName(DataRow errorReader)
        {
            return AnalyseErrors.GetUserName(errorReader);
        }

        /// <summary>
        /// Error: 18456, State: 7
        /// </summary>
        public static void LoginFailedSecurityLoginDisabled(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);

            string userNameProvided = GetUserName(errorReader);
            atsReport.WriteFormat(ATSDriverResources.LoginDisabledAndPasswordIsIncorrect, userNameProvided);
            atsReport.Write(ATSDriverResources.Execute);
            atsReport.WriteFormat(TroubleshootQueriesForUser.AlterLogin, userNameProvided);
            atsReport.Write(TroubleshootQueriesForUser.Enable);
            atsReport.Write(TroubleshootQueriesForUser.Go);
            atsReport.Write(ATSDriverResources.ToEnableAndConfirmThatPasswordIsValid);
        }


        /// <summary>
        /// Error: 18456, State: 8
        /// </summary>
        public static void LoginFailedSecurityPasswordNotMatch(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);

            string userNameProvided = GetUserName(errorReader);
            atsReport.WriteFormat(ATSDriverResources.PasswordForLoginIsIncorrect, userNameProvided);
        }

        /// <summary>
        /// Error: 18456, State: 9
        /// </summary>
        public static void LoginFailedSecurityBadPassword(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            //Debug.Assert(false, "If you get here, please conntact lyudmilf to investigate the problem.");

            WriteErrorAndTime(errorReader, atsReport);

            AnalyseErrors.CheckApiFailures(errorReader, atsReport, true);

            atsReport.Write(ATSDriverResources.DisableCheckPolicy);
        }

        /// <summary>
        /// Error: 18456, State: 10
        /// </summary>
        public static void LoginFailedSecurityBadResult(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);

            AnalyseErrors.CheckApiFailures(errorReader, atsReport, true);

            atsReport.Write(ATSDriverResources.DisableCheckPolicy);
        }

        /// <summary>
        /// Error: 18456, State: 11
        /// </summary>
        public static void LoginFailedSecurityFastCheckServerAccess1(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            SqlDataReader serverReader = null;
            SqlCommand serverCommand = null;
            try
            {
                Tuple<string> userNameAndStatus = CheckWhetherPermissionWasRevokedFromPrimaryPrincipal(errorReader, serverConnection, atsReport, ref serverCommand, ref serverReader);
                string userNameProvided = userNameAndStatus.Field1;
                string status = userNameAndStatus.Field2;
                //If no perm has been Denied and primary principal hasn't been revoked Connect SQL - must be Win Group denied ConnectSQL
                if (status == Constants.AtsDriver.Permissions.Granted)
                {
                    serverCommand = new SqlCommand(string.Format(Queries.WindowsGroupsDeniedPermission, userNameProvided), serverConnection);
                    serverReader = serverCommand.ExecuteReader();
                    if (serverReader.HasRows)
                    {
                        atsReport.WriteFormat(ATSDriverResources.ConnectSqlPermissionHasBeenDeniedToAtLeastOneWindowsGroup, userNameProvided);
                        atsReport.Write(ATSDriverResources.FollowingWindowsGroupsDeniedToConnect);
                    }
                    while (serverReader.Read())
                    {
                        atsReport.Write(serverReader.GetValue(0).ToString());
                    }
                    CloseReader(serverReader);
                }

                atsReport.WriteFormat(ATSDriverResources.PermissionToConnectToSpecifiedEndpoint, userNameProvided);
                atsReport.Write(ATSDriverResources.WinVistaUAC);
                atsReport.Write(ATSDriverResources.ThereforeSQLServerDoesnotRecognizeBUILTIN_AdminGroup);
                atsReport.Write(ATSDriverResources.AddThisWindowsAccountExplicitly);

                //Now let's look at the failed APIs
                AnalyseErrors.CheckApiFailures(errorReader, atsReport, false);
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }

        /// <summary>
        /// Error: 18456, State: 12
        /// </summary>
        public static void LoginFailedSecurityFastCheckServerAccess2(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            SqlCommand serverCommand = null;
            SqlDataReader serverReader = null;
            try
            {
                Tuple<string> userNameAndStatus = CheckWhetherPermissionWasRevokedFromPrimaryPrincipal(errorReader, serverConnection, atsReport, ref serverCommand, ref serverReader);
                string userNameProvided = userNameAndStatus.Field1;

                atsReport.WriteFormat(ATSDriverResources.PermissionToConnectToSpecifiedEndpoint, userNameProvided);

                //Now let's look at the failed APIs
                AnalyseErrors.CheckApiFailures(errorReader, atsReport, true);
            }

            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }

        /// <summary>
        /// Returns tuple with two fields. First one contains provided user name, second - status.
        /// </summary>
        private static Tuple<string> CheckWhetherPermissionWasRevokedFromPrimaryPrincipal(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport, ref SqlCommand serverCommand, ref SqlDataReader serverReader)
        {
            string userNameProvided;
            string status;
            LoginFailedCheck(errorReader, serverConnection, atsReport, ref serverReader, ref serverCommand, out userNameProvided, out status);
            //If no perm has been Denied check if connect permission was revoked from primary principal
            if (status == Constants.AtsDriver.Permissions.Granted)
            {
                serverCommand = new SqlCommand(string.Format(Queries.ConnectSqlRevoked, userNameProvided), serverConnection);
                serverReader = serverCommand.ExecuteReader();

                if (serverReader.HasRows)
                {
                    atsReport.WriteFormat(ATSDriverResources.ConnectSqlPermissionHasBeenRevoked, userNameProvided);
                    status = Constants.AtsDriver.Permissions.Revoked;
                }
                CloseReader(serverReader);
            }
            return new Tuple<string>(userNameProvided, status);
        }

        private static void LoginFailedCheck(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport, ref SqlDataReader serverReader, ref SqlCommand serverCommand, out string userNameProvided, out string status)
        {
            WriteErrorAndTime(errorReader, atsReport);

            userNameProvided = AnalyseErrors.GetUserName(errorReader);
            LinkedList<string> loginTokens = new LinkedList<string>();
            //LoginTokens.AddLast(UserNameProvided);
            AnalyseErrors.GetLoginTokenMembership(serverConnection, loginTokens, string.Format(Queries.GetLoginToken, userNameProvided), false);
            status = Constants.AtsDriver.Permissions.Granted;

            foreach (string loginToken in loginTokens)
            {
                serverCommand = new SqlCommand(string.Format(Queries.ConnectPerms, loginToken), serverConnection);
                serverReader = serverCommand.ExecuteReader();

                while (serverReader.Read())
                {
                    string @class = serverReader.GetValue(0).ToString();
                    string permState = serverReader.GetValue(1).ToString();
                    string grantor = serverReader.GetValue(3).ToString();
                    if (@class.Equals(Constants.AtsDriver.Classes.Endpoint, StringComparison.OrdinalIgnoreCase))
                    {
                        string endpoint = serverReader.GetValue(2).ToString();
                        if (permState.Equals(Constants.AtsDriver.Permissions.Denied, StringComparison.OrdinalIgnoreCase))
                        {
                            atsReport.WriteFormat(ATSDriverResources.ConnectPermissionOnEndpoint, endpoint);
                            atsReport.WriteFormat(ATSDriverResources.ConnectPermissionTo, loginToken);
                            atsReport.WriteFormat(ATSDriverResources.DeniedBy, grantor);
                            status = Constants.AtsDriver.Permissions.Denied;
                        }
                    }
                    else if (@class.Equals(Constants.AtsDriver.Classes.Server, StringComparison.OrdinalIgnoreCase))
                    {
                        if (permState.Equals(Constants.AtsDriver.Permissions.Denied, StringComparison.OrdinalIgnoreCase))
                        {
                            atsReport.WriteFormat(ATSDriverResources.ConnectSqlPermissionHasBeenDeniedTo, loginToken);
                            atsReport.WriteFormat(ATSDriverResources.DeniedBy, grantor);
                            status = Constants.AtsDriver.Permissions.Denied;
                        }
                    }

                }
                CloseReader(serverReader);
            }
        }

        /// <summary>
        /// Error: 18456, State: 15
        /// </summary>
        public static void LoginFailedLoginSwitchDb(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            string userNameProvided = GetUserName(errorReader);

            string databaseName = errorReader[Constants.AtsDriver.DataBaseNameColumn].ToString();
            SqlDataReader serverReader = null;

            atsReport.WriteFormat(ATSDriverResources.LoginDoesnotHaveAccessToTheCurrentDb, userNameProvided);
            atsReport.WriteFormat(ATSDriverResources.CurrentDbIs, databaseName);
            try
            {
                if (!databaseName.Equals(string.Empty))
                {
                    SqlCommand serverCommand = new SqlCommand(string.Format(Queries.DbsStatus, databaseName), serverConnection);
                    serverReader = serverCommand.ExecuteReader();
                    DataTable dbResultTable = new DataTable();
                    if (serverReader.HasRows)
                    {
                        dbResultTable.Load(serverReader);
                    }
                    CloseReader(serverReader);

                    foreach (DataRow dr in dbResultTable.Rows)
                    {
                        //1. What is the access mode?
                        if (!dr[Constants.AtsDriver.AccessModeColumn].ToString().Equals(Constants.AtsDriver.AccessModes.MultiUser, StringComparison.OrdinalIgnoreCase))
                        {
                            atsReport.WriteFormat(ATSDriverResources.AccessModeOfTheDbIs, dr[Constants.AtsDriver.AccessModeColumn].ToString());
                            continue;
                        }

                        //2. Does mapping user exist?
                        AnalyseErrors.CheckUserMapping(serverConnection, atsReport, userNameProvided, databaseName);
                    }
                }
                else
                {
                    //We shouldn't get here.
                    atsReport.Write(ATSDriverResources.InvalidCurrentDb);
                }
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }

        private static void HandleException(Exception ex)
        {
            Logging.Handle(ex);
            Console.WriteLine(ATSDriverResources.SqlExceptionVerifyThatItWorking);
            Console.WriteLine(ATSDriverResources.SeeErrorLogsForDetails);
        }

        /// <summary>
        /// Error: 18456, State: 38
        /// </summary>
        public static void LoginFailedLoginSessionDbUseDbExplicit(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            string userNameProvided = GetUserName(errorReader);

            Console.WriteLine(ATSDriverResources.MoreInfoNeeded);
            Console.WriteLine(ATSDriverResources.YouTryingToConnectToAnExplicitlySpecifiedDb);
            string databaseName = EnterDatabaseName();
            SqlDataReader serverReader = null;

            try
            {
                if (!databaseName.Equals(string.Empty))
                {
                    SqlCommand serverCommand = new SqlCommand(string.Format(Queries.DbsStatus, databaseName), serverConnection);
                    serverReader = serverCommand.ExecuteReader();
                    //1. Does db exist?
                    if (!serverReader.HasRows)
                    {
                        atsReport.WriteFormat(ATSDriverResources.DbDoesnotExistOnThisInstance, databaseName);
                        atsReport.Write(ATSDriverResources.VerifyThatYouSpelledTheNameCorrectly);
                        atsReport.Write(ATSDriverResources.DbAvailable);
                        CloseReader(serverReader);
                        serverCommand.CommandText = Queries.DbsExist;
                        serverReader = serverCommand.ExecuteReader();
                        while (serverReader.Read())
                        {
                            atsReport.Write(serverReader.GetValue(0).ToString());
                        }
                        CloseReader(serverReader);
                        serverCommand.CommandText = string.Format(Queries.SelectSOUNDEXofDatabases, databaseName);
                        serverReader = serverCommand.ExecuteReader();
                        while (serverReader.Read())
                        {
                            atsReport.Write(ATSDriverResources.DidYouMeanThisDb);
                            atsReport.Write(serverReader.GetValue(0).ToString());
                        }
                    }
                    else
                    {
                        DataTable dbResultTable = new DataTable();
                        dbResultTable.Load(serverReader);
                        CloseReader(serverReader);
                        MakeAnalysisOfFailedLoginSessionDb(serverConnection, atsReport, userNameProvided, databaseName, dbResultTable, true);
                    }
                }
                else
                {
                    //We don't know the db name. Give generic recommendations.
                    atsReport.Write(ATSDriverResources.DbIsNotAccessible);
                    atsReport.Write(ATSDriverResources.DbCanBeNotAccessibleBecause);
                    atsReport.Write(ATSDriverResources.ToCheckTheDbStatusUseTheFollowingQuery);
                    atsReport.Write(TroubleshootQueriesForUser.SelectFromDatabases);
                    atsReport.Write(TroubleshootQueriesForUser.Go);
                    atsReport.Write(ATSDriverResources.ToCheckThatTheLoginHasAMappedUserRunFollowingQuery);
                    atsReport.Write(TroubleshootQueriesForUser.UseDb);
                    atsReport.Write(TroubleshootQueriesForUser.Go);
                    atsReport.Write(TroubleshootQueriesForUser.SelectFromDbPrincipals);
                    atsReport.Write(TroubleshootQueriesForUser.Go);
                }
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }

        private static string EnterDatabaseName()
        {
            EnterDBNameForm form = new EnterDBNameForm();
            form.ShowDialog();
            return form.DBName;
        }

        /// <summary>
        /// Error: 18456, State: 40
        /// </summary>
        public static void LoginFailedLoginSessionDbUseDbImplicit(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            string userNameProvided = GetUserName(errorReader);

            string databaseName = string.Empty;
            SqlDataReader serverReader = null;

            try
            {
                SqlCommand serverCommand = new SqlCommand(string.Format(Queries.SelectDefaultDb, userNameProvided), serverConnection);
                serverReader = serverCommand.ExecuteReader();
                while (serverReader.Read())
                {
                    databaseName = serverReader.GetValue(0).ToString();
                }
                CloseReader(serverReader);

                if (!databaseName.Equals(string.Empty))
                {
                    serverCommand.CommandText = string.Format(Queries.DbsStatus, databaseName);
                    serverReader = serverCommand.ExecuteReader();
                    //1. Does db exist?
                    if (!serverReader.HasRows)
                    {
                        atsReport.WriteFormat(ATSDriverResources.DbDoesnotExistOnThisInstance, databaseName);
                        atsReport.Write(ATSDriverResources.IfTheDbHasBeenDroppedYouMustAlterTheLogin);
                    }
                    else
                    {
                        DataTable dbResultTable = new DataTable();
                        dbResultTable.Load(serverReader);
                        CloseReader(serverReader);
                        MakeAnalysisOfFailedLoginSessionDb(serverConnection, atsReport, userNameProvided, databaseName, dbResultTable, true);
                    }
                }
                else
                {
                    //We shouldn't get here.
                    atsReport.Write(ATSDriverResources.InvalidDefaultDbOrLoginDoesnotExist);
                }
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
        }

        private static bool MakeAnalysisOfFailedLoginSessionDb(SqlConnection serverConnection, ReportDetails atsReport, string userNameProvided, string databaseName, DataTable dbResultTable, bool fAccessible)
        {
            foreach (DataRow dataRow in dbResultTable.Rows)
            {
                atsReport.WriteFormat(ATSDriverResources.DbOnlineNotRestricted, databaseName);
                //2. Is it online?
                if (!dataRow[Constants.AtsDriver.StateDescriptionColumn].ToString().Equals(Constants.AtsDriver.Descriptions.Online, StringComparison.OrdinalIgnoreCase))
                {
                    fAccessible = false;
                    atsReport.WriteFormat(ATSDriverResources.StatusOfDbIs, dataRow[Constants.AtsDriver.StateDescriptionColumn].ToString());
                }

                //3. What is the access mode?
                if (!dataRow[Constants.AtsDriver.AccessModeColumn].ToString().Equals(Constants.AtsDriver.AccessModes.MultiUser, StringComparison.OrdinalIgnoreCase))
                {
                    fAccessible = false;
                    atsReport.WriteFormat(ATSDriverResources.AccessModeOfTheDbIs, dataRow[Constants.AtsDriver.AccessModeColumn].ToString());
                }

                //4. Is it a mirror?
                if (dataRow[Constants.AtsDriver.MirroringDescriptionColumn].ToString().Equals(Constants.AtsDriver.Descriptions.Mirror, StringComparison.OrdinalIgnoreCase))
                {
                    fAccessible = false;
                    atsReport.WriteFormat(ATSDriverResources.DbIsAMirrorOfTheDb, dataRow[Constants.AtsDriver.MirroringDescriptionColumn].ToString());
                }

                //5. Does mapping user exist?
                if (fAccessible)
                {
                    AnalyseErrors.CheckUserMapping(serverConnection, atsReport, userNameProvided, databaseName);
                }
            }
            return fAccessible;
        }

        /// <summary>
        /// Error: 18456, State: 52-56
        /// </summary>
        public static void LoginFailedSecurityImpersonationOrToken(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);
            //Let's look at the failed APIs
            AnalyseErrors.CheckApiFailures(errorReader, atsReport, false);
        }

        /// <summary>
        /// Error: 18456, State: 58
        /// </summary>
        public static void LoginFailedWindowsAuthentificatinOnlyError(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            WriteErrorAndTime(errorReader, atsReport);

            atsReport.Write(ATSDriverResources.ServerEngineIsUsingWindowsAuth);
            atsReport.Write(ATSDriverResources.ToConnectUsingServerAuthChangeServerAuthToWinAuthMode);
            atsReport.Write(ATSDriverResources.ToChangeItDoTheFollowingSteps);
        }

        /// <summary>
        /// Error: 18452, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLoginInvalidConnect(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) && 
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);

            atsReport.Write(ATSDriverResources.NoHandshakeThisCouldBeUnderTheFollowingReasons);

            //Let's look at the failed APIs
            XPathNodeIterator iApiErrors = AnalyseErrors.GetApiErrors(errorReader[Constants.AtsDriver.RbErrorColumn].ToString());
            if (iApiErrors != null)
            {
                foreach (XPathNavigator nodeApiErrors in iApiErrors)
                {
                    //Report only Win APIs failed on the same session
                    if (!nodeApiErrors.GetAttribute(Constants.AtsDriver.ApiFailuresAttributes.SPID, string.Empty).Equals(errorReader[Constants.AtsDriver.SessionIdColumn].ToString()) &&
                        AnalyseErrors.IsWinApi(nodeApiErrors.GetAttribute(Constants.AtsDriver.ApiFailuresAttributes.ApiName, string.Empty)))
                    {
                        continue;
                    }

                    string errorCode = nodeApiErrors.GetAttribute(Constants.AtsDriver.ApiFailuresAttributes.ErrorCode, string.Empty);
                    atsReport.WriteFormat(ATSDriverResources.MoreInfoTheFollowingWinAPIFailed, nodeApiErrors.GetAttribute(Constants.AtsDriver.ApiFailuresAttributes.ApiName, string.Empty));
                    string winErrorMessage = AnalyseErrors.GetErrorMessage(errorCode);
                    atsReport.WriteFormat(ATSDriverResources.WithError, errorCode);
                    atsReport.Write(winErrorMessage);
                    atsReport.Write(ATSDriverResources.CheckMSDNToSeeWhatCouldCauseThisError);
                }
            }

            SetLastErrorAndStatus(errorReader);
        }

        private static void SetLastErrorAndStatus(DataRow errorReader)
        {
            AnalyseErrors.LastError = (int)errorReader[Constants.AtsDriver.ErrorNumberColumn];
            AnalyseErrors.LastStatus = (int)errorReader[Constants.AtsDriver.ErrorStateColumn];
        }

        /// <summary>
        /// Error: 18462-18468, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLogonPasswordChangeFailed(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) &&
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);
            SetLastErrorAndStatus(errorReader);
        }

        /// <summary>
        /// Error: 18470, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLoginDisabled(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) &&
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);

            string userNameProvided = GetUserName(errorReader);
            atsReport.WriteFormat(ATSDriverResources.LoginIsDisabled, userNameProvided);
            atsReport.Write(ATSDriverResources.Execute);
            atsReport.WriteFormat(TroubleshootQueriesForUser.AlterLogin, userNameProvided);
            atsReport.Write(TroubleshootQueriesForUser.Enable);
            atsReport.Write(TroubleshootQueriesForUser.Go);
            atsReport.Write(ATSDriverResources.ToEnableTheLogin);

            SetLastErrorAndStatus(errorReader);
        }

        /// <summary>
        /// Error: 18486, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLogonAccountLockedOut(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) &&
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);
            string userNameProvided = GetUserName(errorReader);

            atsReport.WriteFormat(ATSDriverResources.LoginLockedOut, userNameProvided);
            atsReport.Write(ATSDriverResources.ToUnlockExecute);
            atsReport.WriteFormat(TroubleshootQueriesForUser.AlterLogin, userNameProvided);
            atsReport.Write(TroubleshootQueriesForUser.WithPasswordUnlock);
            atsReport.Write(TroubleshootQueriesForUser.Go);

            atsReport.Write(ATSDriverResources.IssueWithWindowsServer2003);

            SetLastErrorAndStatus(errorReader);
        }

        /// <summary>
        /// Error: 18487, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLogonPasswordExpired(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) &&
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);
            string userNameProvided = GetUserName(errorReader);

            atsReport.Write(ATSDriverResources.TheLoginPasswordMustBeChanged);
            atsReport.Write(ATSDriverResources.ToChangeThePasswordExecute);
            atsReport.WriteFormat(TroubleshootQueriesForUser.AlterLogin, userNameProvided);
            atsReport.Write(TroubleshootQueriesForUser.WithPassword);
            atsReport.Write(TroubleshootQueriesForUser.Go);

            SetLastErrorAndStatus(errorReader);
        }

        /// <summary>
        /// Error: 18488, State: ANY_STATE
        /// </summary>
        public static void LoginFailedLogonMustChangePassword(DataRow errorReader, SqlConnection serverConnection, ReportDetails atsReport)
        {
            if ((AnalyseErrors.LastError == (int)errorReader[Constants.AtsDriver.ErrorNumberColumn]) &&
                (AnalyseErrors.LastStatus == (int)errorReader[Constants.AtsDriver.ErrorStateColumn]))
            {
                return;
            }

            WriteErrorAndTime(errorReader, atsReport);
            atsReport.Write(ATSDriverResources.TheLoginPasswordWasSetWithTheMUST_CHANGEOption);
            atsReport.Write(ATSDriverResources.TheLoginPasswordMustBeChanged);
            atsReport.Write(ATSDriverResources.SqlMustPromptForAnUpdatedPassword);

            SetLastErrorAndStatus(errorReader);
        }

        public static void GetStatistics(SqlConnection serverConnection, ReportDetails atsReport, string exePath, string xFileName)
        {
            SqlDataReader serverReader = null;
            try
            {
                SqlCommand serverCommand = new SqlCommand(string.Format(Queries.GetMonitor, exePath, xFileName), serverConnection);
                serverCommand.CommandTimeout = 90;
                serverReader = serverCommand.ExecuteReader();
                if (serverReader.Read())
                {
                    atsReport.WriteFormat(ATSDriverResources.TotalNumberOfErrors, serverReader.GetValue(0).ToString());
                }
                else
                {
                    atsReport.Write(ATSDriverResources.NoLoginFailedErrors);
                }
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                CloseReader(serverReader);

                serverCommand.CommandText = string.Format(Queries.GetErrorsByType, exePath, xFileName);
                serverReader = serverCommand.ExecuteReader();
                atsReport.Write(ATSDriverResources.ErrorsByType);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                atsReport.Write(ATSDriverResources.StatusErrors);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                while (serverReader.Read())
                {
                    atsReport.Write(string.Concat(serverReader.GetValue(0).ToString(), StatisticsValuesSeparator, serverReader.GetValue(1).ToString(), StatisticsSeparator, serverReader.GetValue(2).ToString()));
                }
                CloseReader(serverReader);

                serverCommand.CommandText = string.Format(Queries.GetErrorsByApplication, exePath, xFileName);
                serverReader = serverCommand.ExecuteReader();
                atsReport.Write(ATSDriverResources.ErrorsByClientApplication);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                atsReport.Write(ATSDriverResources.ApplicationNameOfErrors);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                while (serverReader.Read())
                {
                    atsReport.Write(string.Concat(serverReader.GetValue(0).ToString(), StatisticsSeparator, serverReader.GetValue(1).ToString()));
                }
                CloseReader(serverReader);

                serverCommand.CommandText = string.Format(Queries.GetErrorsByClientHost, exePath, xFileName);
                serverReader = serverCommand.ExecuteReader();
                atsReport.Write(ATSDriverResources.ErrorsByClientHost);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                atsReport.Write(ATSDriverResources.HostNameOfErrors);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                while (serverReader.Read())
                {
                    atsReport.Write(string.Concat(serverReader.GetValue(0).ToString(), StatisticsSeparator, serverReader.GetValue(1).ToString()));
                }
                CloseReader(serverReader);

                serverCommand.CommandText = string.Format(Queries.GetErrorsByUser, exePath, xFileName);
                serverReader = serverCommand.ExecuteReader();
                atsReport.Write(ATSDriverResources.ErrorsByUser);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                atsReport.Write(ATSDriverResources.UserNameOfErrors);
                atsReport.Write(ATSDriverResources.StatisticsSeparatorLine);
                while (serverReader.Read())
                {
                    atsReport.Write(string.Concat(serverReader.GetValue(0).ToString(), StatisticsSeparator, serverReader.GetValue(1).ToString()));
                }
                CloseReader(serverReader);
            }
            catch (SqlException se)
            {
                HandleException(se);
            }
            finally
            {
                CloseReader(serverReader);
            }
            return;
        }

        private static void CloseReader(SqlDataReader serverReader)
        {
            if (serverReader != null)
            {
                serverReader.Close();
            }
        }
    }
}